﻿using Microsoft.Win32;
using MiniExcelLibs;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.车间
{
    public partial class Form轧硬投入产出表 : UIPage
    {
        int 开始行;
        int 结束行;
        string 工厂编号 = "3001";
        string FormWorkType = "酸连轧";
        public Form轧硬投入产出表()
        {
            InitializeComponent();
        }

        private void uiButton查询_Click(object sender, EventArgs e)
        {
            Guid guid = Guid.NewGuid();
            string sql = @"update a set a.ProductSize=c.ProductSize
                            from 
                            Bus_HardOriginal a,Bus_PicklingOutput b
                            ,Bus_PlanPickling c
                            where 
                             REPLACE(a.OriginalVolumeNew,'PHS','')=replace(b.OutputVolumeNew,'PHS','') and replace(b.VolumeCodeNew,'PHR','')=replace(c.VolumeCodeNew,'PHR','')
                            AND a.ProductSize IS NULL ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = @"update a set a.OriginalAddress=c.address
                            from 
                            Bus_HardOriginal a,Bus_PicklingOutput b
                            ,Bus_PlanPickling c
                            where 
                             REPLACE(a.OriginalVolumeNew,'PHS','')=replace(b.OutputVolumeNew,'PHS','') and replace(b.VolumeCodeNew,'PHR','')=replace(c.VolumeCodeNew,'PHR','')
                            AND a.OriginalAddress IS NULL ";
            pub.ExecuteSql(pub.ConnectionString, sql);

            //DataTable tb = ListData();
            DataTable tb = ListData(guid.ToString());
            uiDataGridView1.AutoGenerateColumns = false;
            this.uiDataGridView1.DataSource = tb;
            //this.uiDataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            this.uiDataGridView1.Refresh();
            deleteTable(guid.ToString());
        }

        private DataTable ListData(string guid)
        {
            string sql;
            decimal 总投入重量 = 0;
            decimal 总产出重量 = 0;

            sql = "SELECT  '" + guid + "', row_number() over(order by 投入卷号) 序号, 投入卷号, 投入规格, 投入重量, 投入材质, 生产日期, 产出卷号, 产出规格, 产出材质, 产出重量, 产出等级, 去向, 产线名称, ";
            sql += " 班组名称, 班别,客户,产地,成品规格,报工人,报工时间,质检信息 FROM      View_轧硬投入产出明细表 ";
            sql += " where 1=1 ";
            //sql +=  " and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            //sql += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";
            string sql2 = "SELECT distinct 投入卷号,投入重量  FROM      View_轧硬投入产出明细表 ";
            sql2 += " where 1=1  and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            sql2 += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            if (uiTextBox投入卷号包含.Text != "")
            {
                sql += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text + "%'";
                sql2 += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text + "%'";
            }
            if (uiTextBox产出卷号包含.Text != "")
            {
                sql += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text + "%'";
                sql2 += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text + "%'";
            }
            if (this.uiComboBox查询产线.SelectedText != "全部")
            {
                sql += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
                sql2 += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
            }
            if (this.uiComboBox查询班组.SelectedText != "全部" && this.uiComboBox查询班组.SelectedText != "")
            {
                sql += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
                sql2 += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
            }
            if (this.uiComboBox查询等级.SelectedText != "全部")
            {
                sql += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
                sql2 += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
            }
            sql += " order by 报工时间,投入卷号";

            //塞入酸洗投入产出表
            string sqlInsert = @"insert into [dbo].[Bus_轧硬投入产出] (
                                [guid],[序号],[投入卷号],[投入规格],[投入重量],[投入材质],[生产日期],[产出卷号],[产出规格],[产出材质],[产出重量],[产出等级],[去向],[产线名称],[班组名称],[班别],[客户],[产地],[成品规格]
                                ,[报工人],[报工时间],质检信息)";

            sqlInsert += sql;
            pub.ExecuteSql(pub.ConnectionString, sqlInsert);

            //计算分摊重量
            //镀锌投入产出分摊(guid.ToString(), this.uiDatePicker开始日期.Value, this.uiDatePicker结束日期.Value.AddDays(1));
            sql = "  update a set a.产出汇总重量=b.产出汇总重量   from [Bus_轧硬投入产出] a ,(select 投入卷号 ,sum(产出重量) 产出汇总重量 ";
            sql += " from [Bus_轧硬投入产出] where guid='" + guid + "' group by 投入卷号 ) b  where a.投入卷号=b.投入卷号 and a.guid='" + guid + "' ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "     update [Bus_轧硬投入产出] set  分摊重量=投入重量*产出重量/产出汇总重量 where guid='" + guid + "'  ";
            pub.ExecuteSql(pub.ConnectionString, sql);

            //查询
            sql = "select * from Bus_轧硬投入产出 where guid = '" + guid + "' and 生产日期 >= '" + this.uiDatePicker开始日期.Value.ToString("yyyy-MM-dd") + "' and 生产日期 < '" + uiDatePicker结束日期.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";


            DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
            if (tb != null && tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    //等级
                    string 等级 = tb.Rows[i]["产出等级"].ToString();
                    if (等级.ToUpper().Contains("A") || 等级.ToUpper().Contains("B"))
                    {
                        decimal.TryParse(tb.Rows[i]["产出重量"].ToString(), out decimal b);
                        总产出重量 += b;
                    }
                }
            }
            DataTable tb2 = pub.GetDataTable(pub.ConnectionString, sql2);
            if (tb2 != null && tb2.Rows.Count > 0)
            {
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    decimal.TryParse(tb2.Rows[i]["投入重量"].ToString(), out decimal a);
                    总投入重量 += a;
                }
            }
            if (总投入重量 > 0)
            {
                this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
            }
            else
            {
                this.uiTextBox产出率.Text = "";
            }
            this.uiTextBox投入重量合计.Text = 总投入重量.ToString();
            this.uiTextBox产出重量合计.Text = 总产出重量.ToString();
            this.uiTextBox卷数.Text = tb.Rows.Count.ToString();

            return tb;
        }

        private void Form轧硬投入产出表_Load(object sender, EventArgs e)
        {
            this.uiDatePicker开始日期.Value = DateTime.Today.AddDays(-1);
            this.uiDatePicker结束日期.Value = DateTime.Today.AddDays(-1);
            pub.ListRankAll(pub.全局工厂编号, this.uiComboBox查询等级);
            pub.ListWorkLineAll(pub.全局工厂编号, FormWorkType, this.uiComboBox查询产线);
            pub.ListWorkTeamAll(pub.全局工厂编号, "2001-01-0102", this.uiComboBox查询班组);
        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;
            string guid = Guid.NewGuid().ToString();
            DataTable tb = ListData(guid);
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, tb, true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void uiDataGridView1_CellMouseMove(object sender, DataGridViewCellMouseEventArgs e)
        {

        }

        private void uiDataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            开始行 = e.RowIndex;
        }

        private void uiDataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            decimal 总投入重量 = 0;
            decimal 投入重量 = 0;
            decimal 总产出重量 = 0;

            string guid = Guid.NewGuid().ToString();
            int 总卷数 = 0;
            结束行 = e.RowIndex;
            if (开始行 == 0 && 结束行 == 0)
            {
                //选择了就取消 没选择就选择
                //if (this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value != null && (bool)this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value == true)//打钩
                //{
                //    this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value = false;
                //}
                //else
                //if (this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value == null || (bool)this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value == false)
                //{
                //    this.uiDataGridView1.Rows[开始行].Cells["E选择"].Value = true;

                //}
                this.uiTextBox卷数.Text = "1";
                this.uiTextBox投入重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["C投入重量"].Value.ToString();
                this.uiTextBox产出重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["C产出重量"].Value.ToString();
            }
            if (开始行 >= 0 && 结束行 > 0)
            {
                if (开始行 > 结束行)
                {
                    int k = 开始行;
                    开始行 = 结束行;
                    结束行 = k;
                }
                string sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");
                for (int i = 开始行; i <= 结束行; i++)
                {
                    投入重量 = pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["C投入重量"].Value.ToString());
                    string 投入卷号 = this.uiDataGridView1.Rows[i].Cells["C投入卷号"].Value.ToString();
                    sql = " insert into Temp_投入汇总表 (guid, 卷号, 投入重量) ";
                    sql += " values('" + guid + "','" + 投入卷号 + "','" + 投入重量 + "')";
                    pub.ExecuteSql(pub.ConnectionString, sql, "insert into Temp_投入汇总表 ");

                    总产出重量 += pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["C产出重量"].Value.ToString());
                    总卷数++;
                }
                sql = "select  isnull(  sum(t.投入重量),0)  from  (select distinct 卷号, 投入重量 from Temp_投入汇总表 where guid='" + guid + "' ) t ";
                DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
                if (tb.Rows.Count > 0)
                {
                    总投入重量 = pub.Todecimal(tb.Rows[0][0].ToString());
                }
                else
                {
                    总投入重量 = 0;
                }
                sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");

                if (总投入重量 != 0)
                {
                    this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
                }
                else
                {
                    this.uiTextBox产出率.Text = "";
                }
                this.uiTextBox卷数.Text = 总卷数.ToString();
                this.uiTextBox投入重量合计.Text = Math.Round(总投入重量, 3).ToString();
                this.uiTextBox产出重量合计.Text = Math.Round(总产出重量, 3).ToString();
            }

        }

        private void deleteTable(string guid)
        {
            string sql = "delete from Bus_轧硬投入产出 where guid = '" + guid + "';delete from Temp_投入产出表 where guid = '" + guid + "';";
            pub.ExecuteSql(pub.ConnectionString, sql);
        }
    }
}
